package com.hspedu.com.jdbc.datasource;

import com.hspedu.com.jdbc.utils.Actor;
import com.hspedu.com.jdbc.utils.JDBCUtilsByDruid;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author DL5O
 * @version 1.0
 */
public class DBUtils_USE {

    //使用apache-DBUtils 工具类 + druid 完成对表的crud操作
    @Test
    public void testQueryMany() throws SQLException {//返回结果是多行的情况
        //1.得到 连接 (druid)
        Connection connection = JDBCUtilsByDruid.getConnection();
        //2.使用DBUtils 类和接口，先引入DBUtils 相关的jar，加入到本Project
        //3.创建 QueryRunner
        QueryRunner queryRunner = new QueryRunner();
        //4.就可以执行相关的方法了，返回ArrayList 结果集
        String sql = "select id,name from actor where id >= ?";
        //老韩解读
        //(1) query 方法就是执行一个sql语句并得到一个 resultSet 封装到 ArrayList 集合中
        //(2) 返回集合
        //(3) connection: 连接
        //(4) sql: 执行sql语句
        //(5) new BeanListHandler<>(Actor.class): 在将resultSet -> Actor 对象 -> 封装到ArrayList
        //    底层使用反射机制 去获取Actor 类的属性
        //(6) 1 就是给？赋值,可以有多个值,因为是可变参数 Object... params
        //(7) 得到的resultSet，会在query 关闭
        List<Actor> list =
                queryRunner.query(connection, sql, new BeanListHandler<>(Actor.class), 3);
        System.out.println("输出集合的信息");
        for (Actor actor : list) {
            System.out.println(actor);
        }
        JDBCUtilsByDruid.close(null, null, connection);
    }

    @Test
    //演示 apache-dbutils + druid 完成返回结果是单行
    public void testQuerySingle() throws SQLException {
        Connection connection = JDBCUtilsByDruid.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        String sql = "select * from actor where id = ?";

        //因为我们返回的是单条记录，所以使用的Hander 是 BeanHandler
        Actor query = queryRunner.query(connection, sql, new BeanHandler<>(Actor.class), 1);
        if (query != null) {
            System.out.println(query);
        }
        //关闭资源
        JDBCUtilsByDruid.close(null, null, connection);
    }

    @Test
    //演示apache-dbutils + druid 完成查询结果是单行单列->返回的就是object
    public void testScalar() throws SQLException {
        Connection connection = JDBCUtilsByDruid.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        //返回的是单行单列，返回的就是Object
        String sql = "select name from actor where id = ?";

        //因为返回的是一个对象，使用的handler 就是 ScalarHandler
        Object obj = queryRunner.query(connection, sql, new ScalarHandler(), 1);
        System.out.println(obj);//小龙
        //释放资源
        JDBCUtilsByDruid.close(null, null, connection);
    }


    @Test
    //演示apache-dbutils + druid 完成dml(update,insert,delete)
    public void testDML() throws SQLException {
        Connection connection = JDBCUtilsByDruid.getConnection();
        QueryRunner queryRunner = new QueryRunner();

        //这里我们组织sql 完成update，insert，delete
        //String sql = "update actor set name= ? where id = ?";
        //添加
        //String sql = "insert into actor values(null,?,?,?,?)";
        //删除
        String sql = "delete from actor where id = ?";

        //(1) 执行dml 操作是 queryRunner.update()
        //(2) 返回值是受影响的行数
        int affectedRow = queryRunner.update(connection, sql, 5);
        System.out.println(affectedRow > 0 ? "执行成功" : "执行没有影响到表数据");
        JDBCUtilsByDruid.close(null, null, connection);
    }
}
